package com.firesrc.web;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jodd.db.DbThreadSession;
import jodd.db.oom.DbOomQuery;

import com.firesrc.bean.CountBean;

/**
 * Servlet implementation class ViewAllKeyword
 */
public class ViewAllKeyword extends SuperServlet {
	private static final long serialVersionUID = 1L;

	/**
	 * @see HttpServlet#HttpServlet()
	 */
	public ViewAllKeyword() {
		super();
		// TODO Auto-generated constructor stub
	}

	/**
	 * @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doGet(HttpServletRequest request, HttpServletResponse response) throws ServletException, IOException {
		doPost(request, response);
	}

	/**
	 * @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse response)
	 */
	protected void doPost(HttpServletRequest request, HttpServletResponse response) throws ServletException,
			IOException {
		// TODO Auto-generated method stub
		response.setCharacterEncoding("GBK");
		response.setContentType("text/html; charset=gb2312");
		new DbThreadSession(cp);
		String date = request.getParameter("date");
		String keywords = null;
		if (date != null) {

			try {
				keywords = getKeywodByDate(date);
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}

		String groupBy = "group by keyword";
		String sql = "select keyword as name ,count(mail) as count from record  ";
		if (keywords != null) {
			sql = sql + " where keyword in" + keywords;
		}
		sql = sql + groupBy;

		DbOomQuery q = new DbOomQuery(sql);

		ResultSet execute = q.execute();

		CountBean bean = null;
		List<CountBean> list = new ArrayList<CountBean>();
		try {
			while (execute.next()) {
				bean = new CountBean();
				bean.setName(execute.getString(1));
				bean.setCount(execute.getInt(2));
				list.add(bean);
				//				buffer.append("<a href='/QueryMailByKeyword?keyword=" + execute.getString(1) + "' a>"
				//						+ execute.getString(1) + "(" + execute.getInt(2) + ") </a>  &nbsp; &nbsp; ");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		request.setAttribute("list", list);
		request.setAttribute("date", date);
		request.getRequestDispatcher("mailListByKeyword.jsp").forward(request, response);
		//		response.getWriter().println(buffer);
	}

	private String getKeywodByDate(String date) throws SQLException {
		String sql = "select name from keyword  ";
		if (date != null && !date.equals("null")) {
			sql = sql + " where createdate= '" + date + "'";
		}
		DbOomQuery q = new DbOomQuery(sql);
		ResultSet result = q.execute();
		StringBuffer buffer = new StringBuffer();
		buffer.append("(");
		int i = 0;
		while (result.next()) {
			if (i == 0) {
				buffer.append("'");
				buffer.append(result.getString(1));
				buffer.append("'");
			} else {
				buffer.append(',');
				buffer.append("'");
				buffer.append(result.getString(1));
				buffer.append("'");

			}
			i++;
		}
		buffer.append(")");
		return buffer.toString();
	}
}
